package com.lxs.doc.rocess.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * checked有用
 * excel转换为html
 */
public class ExcelConvertToHtmlUtil2 {
    public static void convert(String inputPath, String outputPath) throws Exception {
        try (FileInputStream fis = new FileInputStream(inputPath);
             Workbook workbook = WorkbookFactory.create(fis);
             FileOutputStream fos = new FileOutputStream(outputPath)) {

            StringBuilder html = getHtmlHead();

            for (Sheet sheet : workbook) {
                html.append("<h2>Sheet: ").append(sheet.getSheetName()).append("</h2>");
                html.append("<table >");

                List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
                Set<String> processed = new HashSet<>();
                // 提取当前sheet的图片
                Map<String, String> imageMap = ExtractImageUtil.extractImages(sheet);
                for (Row row : sheet) {
                    html.append("<tr>");
                    for (Cell cell : row) {
                        int rowIdx = cell.getRowIndex();
                        int colIdx = cell.getColumnIndex();
                        // 检查是否包含图片
                        String imgKey = ExtractImageUtil.getCellImageKey(cell);
                        if (imageMap.containsKey(imgKey)) {
                            html.append("<td class='image-cell'>");
                            html.append(imageMap.get(imgKey));
                            html.append("</td>");
                        }
                        // 检查是否为合并区域左上角
                        boolean isTopLeft = false;
                        CellRangeAddress region = null;
                        for (CellRangeAddress r : mergedRegions) {
                            if (r.getFirstRow() == rowIdx &&
                                    r.getFirstColumn() == colIdx &&
                                    !processed.contains(r.toString())) {
                                isTopLeft = true;
                                region = r;
                                processed.add(r.toString());
                                break;
                            }
                        }

                        if (isTopLeft && region != null) {
                            int rowspan = region.getLastRow() - region.getFirstRow() + 1;
                            int colspan = region.getLastColumn() - region.getFirstColumn() + 1;
                            String style = getCellStyleHtml(cell);
                            html.append(String.format("<td rowspan='%d' colspan='%d' style='%s'>%s</td>",
                                    rowspan, colspan, style, getCellValue(cell)));
                        } else if (!isInMergedRegion(sheet, rowIdx, colIdx)) {
                            String style = getCellStyleHtml(cell);
                            html.append(String.format("<td style='%s'>%s</td>", style, getCellValue(cell)));
                        }
                    }
                    html.append("</tr>");
                }
                html.append("</table>");
            }
            html.append("</body></html>");
            fos.write(html.toString().getBytes());
        }
    }

    private static StringBuilder  getHtmlHead() {
        return new StringBuilder("<html><head><style>\n" +
                        "table {\n" +
                        "  border-collapse: collapse !important; \n" +
                        "}\n" +
                        "td, th {\n" +
                        "  border: 1px solid #000 !important; \n" +
                        "  padding: 4px;\n" +
                        "}\n" +
                        ".merged-cell {\n" +
                        "  border-right: none !important;\n" +
                        "  border-bottom: none !important;\n" +
                        "}\n" +
                        ".image-cell {\n" +
                        "    vertical-align: middle;\n" +
                        "    text-align: center;\n" +
                        "}\n"+
                        "</style></head><body>");
    }

    private static String getCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString();
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        } else if (cellType == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else {
            return "";
        }
    }
    private static String getCellStyleHtml(Cell cell) {
        CellStyle style = cell.getCellStyle();
        StringBuilder styleAttr = new StringBuilder();

        // 字体样式
        Font font = cell.getSheet().getWorkbook().getFontAt(style.getFontIndex());
        if (font.getBold()) styleAttr.append("font-weight:bold;");
        if (font.getItalic()) styleAttr.append("font-style:italic;");

        // 背景颜色
        if (style.getFillPattern() != FillPatternType.NO_FILL) {
            String bgColor = getBackgroundColor(cell);
            styleAttr.append("background-color:").append(bgColor).append(";");
            // 在HTML生成时调用
//            styleAttr.append(String.format("background-color:", bgColor)).append(";");
        }

        // 边框
        if (style.getBorderTop() != BorderStyle.NONE) {
            styleAttr.append("border-top:1px solid;");
        }

        switch (style.getAlignment()) {
            case CENTER:
                styleAttr.append("text-align:center;");
                break;
            case RIGHT:
                styleAttr.append("text-align:right;");
                break;
            case LEFT:
            default:
                styleAttr.append("text-align:left;");
        }

        // 垂直对齐映射
        switch (style.getVerticalAlignment()) {
            case CENTER: styleAttr.append("vertical-align:middle;"); break;
            case TOP: styleAttr.append("vertical-align:top;"); break;
            case BOTTOM: styleAttr.append("vertical-align:bottom;"); break;
        }
        return styleAttr.toString();
    }
    // 获取单元格所在合并区域
    private CellRangeAddress getMergedRegion(Sheet sheet, int rowIdx, int colIdx) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(rowIdx, colIdx) &&
                    rowIdx == region.getFirstRow() &&
                    colIdx == region.getFirstColumn()) {
                return region;
            }
        }
        return null;
    }

    // 判断是否在合并区域内（非左上角）
    private static boolean isInMergedRegion(Sheet sheet, int rowIdx, int colIdx) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(rowIdx, colIdx) &&
                    !(rowIdx == region.getFirstRow() && colIdx == region.getFirstColumn())) {
                return true;
            }
        }
        return false;
    }
    private static String getBackgroundColor(Cell cell) {
        CellStyle style = cell.getCellStyle();
        if (style.getFillPattern() == FillPatternType.NO_FILL) {
            return "transparent";
        }

        if (cell.getSheet().getWorkbook() instanceof XSSFWorkbook) {
            XSSFCellStyle xssfStyle = (XSSFCellStyle) style;
            XSSFColor fgColor = xssfStyle.getFillForegroundColorColor();
            return fgColor != null ? toHex(fgColor) : "transparent";
        } else {
            HSSFCellStyle hssfStyle = (HSSFCellStyle) style;
            short bgIndex = hssfStyle.getFillBackgroundColor();
            HSSFPalette palette = ((HSSFWorkbook) cell.getSheet().getWorkbook()).getCustomPalette();
            HSSFColor color = palette.getColor(bgIndex);
            return color != null ? toHex(color) : "transparent";
        }
    }
    private static String toHex(HSSFColor color) {
        if (color == null) return "transparent";
        short[] triplet = color.getTriplet();
        return String.format("#%02X%02X%02X", triplet[0], triplet[1], triplet[2]);
    }
    private static String toHex(XSSFColor color) {
        if (color == null) return "transparent";
        String argb = color.getARGBHex();
        // 去掉前两位Alpha通道，保留RGB
        return "#" + argb.substring(2);
    }
}
